"""
Statistics API routes - Statistics Data
Using raw SQL queries with psycopg2 (NO ORM!)
"""

from flask import Blueprint, request, jsonify
from database.connection import execute_query

bp = Blueprint('statistics', __name__, url_prefix='/api/statistics')

@bp.route('/dashboard', methods=['GET'])
def get_dashboard():
    """
    Get dashboard statistics
    GET /api/statistics/dashboard
    """
    try:
        query = """
            SELECT 
                (SELECT COUNT(*) FROM donors WHERE is_active = true) as active_donors,
                (SELECT COUNT(*) FROM sperm_vials WHERE status = 'Available') as available_vials,
                (SELECT COUNT(*) FROM orders WHERE EXTRACT(YEAR FROM order_date) = EXTRACT(YEAR FROM CURRENT_DATE) AND EXTRACT(MONTH FROM order_date) = EXTRACT(MONTH FROM CURRENT_DATE)) as monthly_orders,
                (SELECT COALESCE(SUM(total_price), 0) FROM orders WHERE EXTRACT(YEAR FROM order_date) = EXTRACT(YEAR FROM CURRENT_DATE) AND EXTRACT(MONTH FROM order_date) = EXTRACT(MONTH FROM CURRENT_DATE) AND status IN ('Paid', 'Shipped', 'Completed')) as monthly_revenue
        """
        
        stats = execute_query(query, fetch_one=True)
        
        return jsonify({
            'code': 200,
            'message': 'success',
            'data': stats
        }), 200
        
    except Exception as e:
        return jsonify({
            'code': 500,
            'message': str(e)
            }), 500

@bp.route('/revenue', methods=['GET'])
def get_monthly_revenue():
    """
    Get monthly revenue statistics
    GET /api/statistics/revenue?month=2025-10
    """
    try:
        month = request.args.get('month')  # Format: YYYY-MM
        
        if month:
            # Get revenue for specific month
            query = """
                SELECT 
                    COALESCE(SUM(total_price), 0) as revenue,
                    COUNT(*) as order_count
                FROM orders
                WHERE TO_CHAR(order_date, 'YYYY-MM') = %s
                AND status IN ('Paid', 'Shipped', 'Completed')
            """
            result = execute_query(query, (month,), fetch_one=True)
        else:
            # Get revenue for last 12 months
            query = """
                SELECT 
                    TO_CHAR(order_date, 'YYYY-MM') as month,
                    COALESCE(SUM(total_price), 0) as revenue,
                    COUNT(*) as order_count
                FROM orders
                WHERE order_date >= CURRENT_DATE - INTERVAL '12 months'
                AND status IN ('Paid', 'Shipped', 'Completed')
                GROUP BY TO_CHAR(order_date, 'YYYY-MM')
                ORDER BY month ASC
            """
            result = execute_query(query)
        
        return jsonify({
            'code': 200,
            'message': 'success',
            'data': result
        }), 200
        
    except Exception as e:
        return jsonify({
            'code': 500,
            'message': str(e)
            }), 500

@bp.route('/blood-type', methods=['GET'])
def get_blood_type_distribution():
    """
    Get blood type distribution
    GET /api/statistics/blood-type
    """
    try:
        query = """
            SELECT 
                blood_type as name,
                COUNT(*) as value
            FROM donors
            WHERE is_active = true
            GROUP BY blood_type
            ORDER BY blood_type
        """
        
        distribution = execute_query(query)
        
        return jsonify({
            'code': 200,
            'message': 'success',
            'data': distribution
        }), 200
        
    except Exception as e:
        return jsonify({
            'code': 500,
            'message': str(e)
            }), 500

@bp.route('/order-trend', methods=['GET'])
def get_order_trend():
    """
    Get order trend
    GET /api/statistics/order-trend?start_date=2025-01-01&end_date=2025-12-31
    """
    try:
        start_date = request.args.get('start_date')
        end_date = request.args.get('end_date')
        
        if start_date and end_date:
            query = """
                SELECT 
                    TO_CHAR(order_date, 'YYYY-MM') as month,
                    COUNT(*) as count
                FROM orders
                WHERE order_date BETWEEN %s AND %s
                GROUP BY TO_CHAR(order_date, 'YYYY-MM')
                ORDER BY month
            """
            params = (start_date, end_date)
        else:
            # Default to last 6 months
            query = """
                SELECT 
                    TO_CHAR(order_date, 'Mon') as month,
                    COUNT(*) as count
                FROM orders
                WHERE order_date >= CURRENT_DATE - INTERVAL '6 months'
                GROUP BY TO_CHAR(order_date, 'YYYY-MM'), TO_CHAR(order_date, 'Mon')
                ORDER BY TO_CHAR(order_date, 'YYYY-MM')
            """
            params = None
        
        trend = execute_query(query, params)
        
        # Format response for frontend charts
        months = [item['month'] for item in trend]
        counts = [item['count'] for item in trend]
        
        return jsonify({
            'code': 200,
            'message': 'success',
            'data': {
                'months': months,
                'counts': counts
            }
        }), 200
        
    except Exception as e:
        print(f"ERROR in get_order_trend: {str(e)}")
        import traceback
        traceback.print_exc()
        return jsonify({
            'code': 500,
            'message': str(e)
        }), 500

@bp.route('/donor-stats', methods=['GET'])
def get_donor_statistics():
    """
    Get comprehensive donor statistics
    GET /api/statistics/donor-stats
    """
    try:
        query = """
            SELECT 
                COUNT(*) as total_donors,
                COUNT(*) FILTER (WHERE is_active = true) as active_donors,
                COUNT(DISTINCT blood_type) as blood_type_count,
                AVG(height_cm) as avg_height,
                AVG(weight_kg) as avg_weight,
                COUNT(DISTINCT education_level) as education_levels
            FROM donors
        """
        
        stats = execute_query(query, fetch_one=True)
        
        return jsonify({
            'code': 200,
            'message': 'success',
            'data': stats
        }), 200
        
    except Exception as e:
        return jsonify({
            'code': 500,
            'message': str(e)
            }), 500

@bp.route('/vial-stats', methods=['GET'])
def get_vial_statistics():
    """
    Get comprehensive vial statistics
    GET /api/statistics/vial-stats
    """
    try:
        query = """
            SELECT 
                COUNT(*) as total_vials,
                COUNT(*) FILTER (WHERE status = 'Available') as available,
                COUNT(*) FILTER (WHERE status = 'Reserved') as reserved,
                COUNT(*) FILTER (WHERE status = 'Used') as used,
                COUNT(*) FILTER (WHERE status = 'Discarded') as discarded,
                AVG(motility_percent) as avg_motility,
                AVG(sperm_count_mil) as avg_count,
                AVG(volume_ml) as avg_volume
            FROM sperm_vials
        """
        
        stats = execute_query(query, fetch_one=True)
        
        return jsonify({
            'code': 200,
            'message': 'success',
            'data': stats
        }), 200
        
    except Exception as e:
        return jsonify({
            'code': 500,
            'message': str(e)
            }), 500

@bp.route('/order-stats', methods=['GET'])
def get_order_statistics():
    """
    Get comprehensive order statistics
    GET /api/statistics/order-stats
    """
    try:
        query = """
            SELECT 
                COUNT(*) as total_orders,
                COUNT(*) FILTER (WHERE status = 'Pending') as pending,
                COUNT(*) FILTER (WHERE status = 'Paid') as paid,
                COUNT(*) FILTER (WHERE status = 'Shipped') as shipped,
                COUNT(*) FILTER (WHERE status = 'Completed') as completed,
                COUNT(*) FILTER (WHERE status = 'Cancelled') as cancelled,
                COALESCE(SUM(total_price) FILTER (WHERE status IN ('Paid', 'Shipped', 'Completed')), 0) as total_revenue,
                COALESCE(AVG(total_price) FILTER (WHERE status IN ('Paid', 'Shipped', 'Completed')), 0) as avg_order_value
            FROM orders
        """
        
        stats = execute_query(query, fetch_one=True)
        
        return jsonify({
            'code': 200,
            'message': 'success',
            'data': stats
        }), 200
        
    except Exception as e:
        return jsonify({
            'code': 500,
            'message': str(e)
            }), 500

@bp.route('/offspring-stats', methods=['GET'])
def get_offspring_statistics():
    """
    Get offspring tracking statistics
    GET /api/statistics/offspring-stats
    """
    try:
        query = """
            SELECT 
                COUNT(*) as total_offspring,
                COUNT(*) FILTER (WHERE gender = 'Male') as male_count,
                COUNT(*) FILTER (WHERE gender = 'Female') as female_count,
                COUNT(DISTINCT donor_id) as donors_with_offspring,
                COUNT(DISTINCT recipient_id) as recipients_with_offspring
            FROM offspring_tracking
        """
        
        stats = execute_query(query, fetch_one=True)
        
        return jsonify({
            'code': 200,
            'message': 'success',
            'data': stats
        }), 200
        
    except Exception as e:
        return jsonify({
            'code': 500,
            'message': str(e)
            }), 500

